package yun.web.activerecord.dialect.mysql;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ArrayUtil;
import yun.web.activerecord.ActiveRecordException;
import yun.web.activerecord.Record;
import yun.web.activerecord.Table;
import yun.web.activerecord.dialect.Dialect;
import yun.web.activerecord.dialect.pg.type.PG;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;


import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import java.util.stream.Collectors;

/**
 * MysqlDialect.
 */
public class MysqlDialect extends Dialect {

    public String forTableBuilderDoBuild(String tableName) {
        return "select * from `" + tableName + "` where 1 = 2";
    }

    public String forFindAll(String tableName) {
        return "select * from `" + tableName + "`";
    }

    public String forModelDeleteById(Table table) {
        String[] pKeys = table.getPrimaryKey();
        StringBuilder sql = new StringBuilder(45);
        sql.append("delete from `");
        sql.append(table.getName());
        sql.append("` where ");
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
        }
        return sql.toString();
    }

    @Override
    public void forModelSave(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras, String... generated_keys) {
        sql.append("insert into `").append(table.getName()).append("`(");
        StringBuilder temp = new StringBuilder(") values(");
        for (Entry<String, Object> e : attrs.entrySet()) {
            String colName = e.getKey();
            if (table.hasColumnLabel(colName)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                    temp.append(", ");
                }
                sql.append('`').append(colName).append('`');
                temp.append('?');
                paras.add(e.getValue());
            }
        }
        sql.append(temp.toString()).append(')');
    }

    public void forModelUpdate(Table table, Map<String, Object> attrs, Set<String> modifyFlag, StringBuilder sql, List<Object> paras) {
        sql.append("update `").append(table.getName()).append("` set ");
        String[] pKeys = table.getPrimaryKey();
        for (Entry<String, Object> e : attrs.entrySet()) {
            String colName = e.getKey();
            if (modifyFlag.contains(colName) && !isPrimaryKey(colName, pKeys) && table.hasColumnLabel(colName)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                }
                sql.append('`').append(colName).append("` = ? ");
                paras.add(e.getValue());
            }
        }
        sql.append(" where ");
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
            paras.add(attrs.get(pKeys[i]));
        }
    }

    @Override
    public void forModelUpsert(Table table, Map<String, Object> attrs, StringBuilder sql, List<Object> paras, String... generated_keys) {
        throw new ActiveRecordException("Mysql upsert 语法尚未实现.jpg");
    }

    public String forModelFindById(Table table, String columns) {
        StringBuilder sql = new StringBuilder("select ");
        columns = columns.trim();
        if ("*".equals(columns)) {
            sql.append('*');
        } else {
            String[] arr = columns.split(",");
            for (int i = 0; i < arr.length; i++) {
                if (i > 0) {
                    sql.append(',');
                }
                sql.append('`').append(arr[i].trim()).append('`');
            }
        }

        sql.append(" from `");
        sql.append(table.getName());
        sql.append("` where ");
        String[] pKeys = table.getPrimaryKey();
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
        }
        return sql.toString();
    }

    public String forDbFindById(String tableName, String[] pKeys) {
        tableName = tableName.trim();
        trimPrimaryKeys(pKeys);

        StringBuilder sql = new StringBuilder("select * from `").append(tableName).append("` where ");
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
        }
        return sql.toString();
    }

    public String forDbDeleteById(String tableName, String[] pKeys) {
        tableName = tableName.trim();
        trimPrimaryKeys(pKeys);

        StringBuilder sql = new StringBuilder("delete from `").append(tableName).append("` where ");
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
        }
        return sql.toString();
    }

    @Override
    public void forDbSave(String tableName, Record record, StringBuilder sql, List<Object> paras, String... generated_keys) {
        tableName = tableName.trim();
        //trimPrimaryKeys(pKeys);	// important

        sql.append("insert into `");
        sql.append(tableName).append("`(");
        StringBuilder temp = new StringBuilder();
        temp.append(") values(");

        for (Entry<String, Object> e : record.getColumns().entrySet()) {
            if (paras.size() > 0) {
                sql.append(", ");
                temp.append(", ");
            }
            sql.append('`').append(e.getKey()).append('`');
            temp.append('?');
            paras.add(e.getValue());
        }
        sql.append(temp.toString()).append(')');
    }


    public void forDbUpdate(String tableName, String[] pKeys, Object[] ids, Record record, StringBuilder sql, List<Object> paras) {
        tableName = tableName.trim();
        trimPrimaryKeys(pKeys);

        sql.append("update `").append(tableName).append("` set ");
        for (Entry<String, Object> e : record.getColumns().entrySet()) {
            String colName = e.getKey();
            if (!isPrimaryKey(colName, pKeys)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                }
                sql.append('`').append(colName).append("` = ? ");
                paras.add(e.getValue());
            }
        }
        sql.append(" where ");
        for (int i = 0; i < pKeys.length; i++) {
            if (i > 0) {
                sql.append(" and ");
            }
            sql.append('`').append(pKeys[i]).append("` = ?");
            paras.add(ids[i]);
        }
    }

    @Override
    public void forDbUpsert(String table_name, String[] p_keys, Record record, StringBuilder sql, List<Object> paras, String... generated_keys) {
        throw new ActiveRecordException("Mysql upsert 语法尚未实现.jpg");
    }

    public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
        int offset = pageSize * (pageNumber - 1);
        findSql.append(" limit ").append(offset).append(", ").append(pageSize);    // limit can use one or two '?' to pass paras
        return findSql.toString();
    }


    @Override
    public void getRecordGeneratedKey(PreparedStatement pst, Record record, String[] generated_keys) throws SQLException {
        ResultSet rs = pst.getGeneratedKeys();
        if (rs.next()) {
            // 测试 mysql自增字段有多个, 自增id, c1(默认值1), c2(默认值字符'1')
            // 但是返回的三个值, 都是 自增id的值
            // 垃圾Mysql Over
            String key = generated_keys[0];
            record.set(key, rs.getObject(1));
        }
        rs.close();
    }
}
